package com.qdlc.p2p.dal.dao.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.persistence.Query;

import com.qdlc.p2p.common.constant.*;
import com.qdlc.p2p.dal.dto.*;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.qdlc.p2p.common.exception.UserException;
import com.qdlc.p2p.common.util.DateUtil;
import com.qdlc.p2p.common.util.NumberUtil;
import com.qdlc.p2p.common.util.Page;
import com.qdlc.p2p.common.util.StringUtil;
import com.qdlc.p2p.dal.checkrule.IndexRuleCheck;
import com.qdlc.p2p.dal.common.RuleCheckHelper;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.common.model.jpa.OrderFilter.OrderType;
import com.qdlc.p2p.dal.common.model.jpa.PageDataList;
import com.qdlc.p2p.dal.common.model.jpa.QueryParam;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter.Operators;
import com.qdlc.p2p.dal.dao.BorrowTenderDao;
import com.qdlc.p2p.dal.model.BorrowTenderModel;
import com.qdlc.p2p.dal.model.RankModel;
import com.qdlc.p2p.dal.model.TenderDynamicModel;

@Repository("borrowTenderDao")
public class BorrowTenderDaoImpl extends BaseDaoImpl<BorrowTender> implements BorrowTenderDao {

	@Override
	public double investIngAmount(long userId) {
		String sql = "SELECT SUM(t.account) FROM " + Global.DB_PREFIX + "borrow b ," + Global.DB_PREFIX + "borrow_tender t WHERE t.borrow_id = b.id AND b.`status` = 1 AND t.user_id = :userId ";
		Query query = em.createNativeQuery(sql);
		query.setParameter("userId", userId);
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj.toString());
		}
		return result;
	}

	@Override
	public double investAmount(long userId) {
		String sql = "SELECT SUM(t.real_amount) FROM " + Global.DB_PREFIX + "borrow b ," + Global.DB_PREFIX + "borrow_tender t WHERE t.borrow_id = b.id AND b.`status` in (1,3,6,7,8) AND t.`status` in (1,3) and b.category != 2 AND t.user_id = :userId ";
		Query query = em.createNativeQuery(sql);
		query.setParameter("userId", userId);
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj.toString());
		}
		return result;
	}

	@Override
	public PageDataList<BorrowTenderModel> list(BorrowTenderModel model) {
		QueryParam param = QueryParam.getInstance();
		if (model != null) {
			if ((model.getUser() != null) && (model.getUser().getUserId() > 0)) {
				param.addParam("user.userId", model.getUser().getUserId());
			}
			if (StringUtil.isNotBlank(model.getUserName())) {
				param.addParam("user.userName", Operators.LIKE, model.getUserName().trim());
			}
			if (model.getStatus() != 99) {
				if (model.getStatus() == 98) {// 前台查询投资人的所有投资
					param.addParam("status", Operators.NOTEQ, -1);
					param.addParam("status", Operators.NOTEQ, 0);
					param.addParam("status", Operators.NOTEQ, 2);
				} else if (model.getStatus() == 49) { // 4/49复审未通过;
					SearchFilter orFilter1 = new SearchFilter("status", Operators.EQ, 4);
					SearchFilter orFilter2 = new SearchFilter("status", Operators.EQ, 49);
					param.addOrFilter(orFilter1, orFilter2);
				} else if (model.getStatus() == 59) { // 5/59用户取消;
					SearchFilter orFilter1 = new SearchFilter("status", Operators.EQ, 5);
					SearchFilter orFilter2 = new SearchFilter("status", Operators.EQ, 59);
					param.addOrFilter(orFilter1, orFilter2);
				} else {
					param.addParam("status", model.getStatus());
				}
			}
			// if (StringUtil.isNotBlank(model.getBorrowName())) {
			// param.addParam("borrow.name", Operators.EQ, model.getBorrowName());
			// }HCB
			if (StringUtil.isNotBlank(model.getStartTime())) {
				Date start = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
				param.addParam("addTime", Operators.GTE, start);
			}
			if (StringUtil.isNotBlank(model.getEndTime())) {
				Date end = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
				param.addParam("addTime", Operators.LTE, end);
			}
			param.addPage(model.getPage(), model.getSize());
		}
		param.addOrder(OrderType.DESC, "id");

		PageDataList<BorrowTender> pageDataList = findPageList(param);
		PageDataList<BorrowTenderModel> pageDateList_ = new PageDataList<BorrowTenderModel>();
		List<BorrowTenderModel> list = new ArrayList<BorrowTenderModel>();
		pageDateList_.setPage(pageDataList.getPage());
		if (pageDataList.getList().size() > 0) {
			for (int i = 0; i < pageDataList.getList().size(); i++) {
				BorrowTender t = pageDataList.getList().get(i);
				BorrowTenderModel tm = BorrowTenderModel.instance(t);
				tm.setBorrowName(t.getBorrow().getName());
				tm.setAccountYes(t.getBorrow().getAccountYes());
				tm.setUserName(t.getUser().getUserName());
				tm.setBorrowUserName(t.getBorrow().getUser().getUserName());
				tm.setScales(t.getBorrow().getScales());
				list.add(tm);
			}
		}
		pageDateList_.setList(list);
		return pageDateList_;
	}

	/** 修改tender的待收本金和待收利息 * */
	@Override
	public void updateRepayTender(double capital, double interest, long id) {
		String jpql = "UPDATE BorrowTender SET repaymentYesAccount = repaymentYesAccount + :repaymentYesAccount,"
				+ "repaymentYesInterest = repaymentYesInterest + :repaymentYesInterest,waitAccount = waitAccount - :waitAccount,"
				+ "waitInterest = waitInterest - :waitInterest WHERE id=:id";
		Query query = em.createQuery(jpql);
		query.setParameter("repaymentYesAccount", capital);
		query.setParameter("repaymentYesInterest", interest);
		query.setParameter("waitAccount", capital);
		query.setParameter("waitInterest", interest);
		query.setParameter("id", id);
		query.executeUpdate();
	}

	@Override
	public double hasTenderTotalPerBorrowByUserid(long borrowId, long userId) {
        StringBuffer sb = new StringBuffer("SELECT IFNULL(SUM(account),0) FROM ");
        sb.append(Global.DB_PREFIX).append("borrow_tender");
        sb.append(" WHERE ");
        sb.append("     borrow_id = :borrow_id ");
        sb.append(" AND user_id = :user_id ");
        sb.append(" AND status <> ").append(BorrowTenderStatus.FAIL);
        sb.append(" AND status <> ").append(BorrowTenderStatus.INVALID);
		Query query = em.createNativeQuery(sb.toString())
                .setParameter("borrow_id", borrowId)
                .setParameter("user_id", userId);
		return Double.parseDouble(query.getSingleResult().toString());
	}

	@Override
	public PageDataList<BorrowTenderModel> list(long borrowId, int page, int size) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("borrow.id", borrowId);
		param.addParam("status",Operators.NOTEQ, BorrowTenderStatus.FAIL);
        param.addParam("status",Operators.NOTEQ, BorrowTenderStatus.INVALID);
		param.addOrder(OrderType.DESC, "addTime");
		if (size != 0) {
			param.addPage(page, size);
		} else {
			param.addPage(page);
		}
		PageDataList<BorrowTender> pageDataList = findPageList(param);
		PageDataList<BorrowTenderModel> pageDataList_ = new PageDataList<BorrowTenderModel>();
		List<BorrowTenderModel> list = new ArrayList<BorrowTenderModel>();
		pageDataList_.setPage(pageDataList.getPage());
		if (pageDataList.getList().size() > 0) {
			for (int i = 0; i < pageDataList.getList().size(); i++) {
				BorrowTender tender = pageDataList.getList().get(i);
				BorrowTenderModel model = BorrowTenderModel.instance(tender);
				String username = tender.getUser().getUserName();
				String mobilePhone = tender.getUser().getHideMobilePhone();
				model.setUser(null);
				model.setBorrow(null);

				if ((size == 0) || (size == 999)) {
					model.setUserName(username.charAt(0) + "******" + username.charAt(username.length() - 1));
					model.setMobilePhone(mobilePhone);
				} else {
					model.setUserName(username);
				}
				// 获取本次投资红包使用金额
				double redAccount = getTenderUseRed(tender.getId(), "borrow_tender");
				model.setUseRadMoney(redAccount);
				// 获取加息券使用额度
				double rateApr = getTenderUseRate(tender.getId());
				model.setUpApr(rateApr);
				// 使用体验金额度
				double experMoney = getTenderUseExper(tender.getId());
				model.setUseExperMoney(experMoney);
				list.add(model);
			}
		}
		pageDataList_.setList(list);
		return pageDataList_;
	}

	/**
	 * 首页排行榜
	 *
	 * @param rank month:月排行 today:日排行 total:总排行
	 * @return 排行列表
	 */
	@Override
	@Cacheable(value="list_rank", key="'ranke_'+#rank")
	public List<RankModel> getRankList(String rank) {
		String selSql = "select p3.user_name as username,sum(p1.account) as tenderMoney,p4.avatar_pic_path from " + Global.DB_PREFIX + "borrow_tender p1"
				+ " left join " + Global.DB_PREFIX + "borrow p2 on p2.id=p1.borrow_id"
				+ " left join " + Global.DB_PREFIX + "user p3 on p3.user_id=p1.user_id left join " + Global.DB_PREFIX + "user_cache p4 "
				+ "on p4.user_id=p1.user_id where ";

		StringBuilder sb = new StringBuilder();
		sb.append(selSql);
		sb.append("(p2.type !=" + BorrowType.TYPE_SECOND + " and p2.status in (1,3,6,7,8)) and p1.status in (1, 3)");// 秒标 不统计
		IndexRuleCheck indexRuleCheck = RuleCheckHelper.getValue(IndexRuleCheck.class);
		IndexRuleCheck.Rank rankRule;
		if ("month".equalsIgnoreCase(rank)) {
			rankRule = indexRuleCheck.monthRank; // 月排行榜
		} else if ("today".equalsIgnoreCase(rank)) {
			rankRule = indexRuleCheck.todayRank;// 日排行榜
		} else {
			rankRule = indexRuleCheck.totalRank;// 总排行榜
		}
		// -1: 总排名 0:当天排名 1:月排名
		if (rankRule.status == 1) { // 月排名
			sb.append(" AND (p2.borrow_time_type <> 1 or p2.time_limit >= " + rankRule.day + ") ");
		} else if (rankRule.status == 0) {
			sb.append(" AND p1.add_time >= :startTime and p1.add_time <= :endTime ");
		}
		sb.append(" group by p3.user_name order by tenderMoney desc").append(" limit 0," + rankRule.count); // 件数
		Query query = em.createNativeQuery(sb.toString());
		if (rankRule.status == 0) {
			query.setParameter("startTime", DateUtil.getTodayFirstIntegralTime());
			query.setParameter("endTime", DateUtil.getTodayLastIntegralTime());
		}
		List<Object[]> ls = query.getResultList();
		List<RankModel> list = Lists.newArrayList();
		for (Object[] obj : ls) {
			RankModel model = new RankModel();
			String userName = obj[0] + "";
			model.setUserName(userName.charAt(0) + "******" + userName.charAt(userName.length() - 1));
			model.setTenderMoney(NumberUtil.getDouble2(obj[1] + ""));
			model.setAvatarPicPath(obj[2] + "");
			list.add(model);
		}
		return list;
	}

	@Override
	@Cacheable(value="list_today_tender", key="'list_today_tender'")
	public List<TenderDynamicModel> getTodayAddTenderList() {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("  p3.user_name,");
		sql.append("  p1.account,");
		sql.append("  p3.mobile_phone,");
		sql.append("  p1.add_time, ");
		sql.append("  p4.avatar_pic_path");
		sql.append(" FROM");
		sql.append("  " + Global.DB_PREFIX + "borrow_tender p1 ");
/*		sql.append("  LEFT JOIN " + Global.DB_PREFIX + "borrow p2 ");
		sql.append("    ON p2.id = p1.borrow_id ");*/
		sql.append("  LEFT JOIN " + Global.DB_PREFIX + "user p3 ");
		sql.append("    ON p3.user_id = p1.user_id ");
		sql.append("  LEFT JOIN " + Global.DB_PREFIX + "user_cache p4 ");
		sql.append("    ON p4.user_id = p1.user_id ");
        sql.append("WHERE ");
/*		sql.append("(    p2.type != 101 "); // 非秒标
		sql.append("    AND p2.status IN (" + BorrowStatus.STATUS_TRIAL_PASSED);
        sql.append("," + BorrowStatus.STATUS_RECHECK_PASS);
        sql.append("," + BorrowStatus.STATUS_REPAYMENT_START);
        sql.append("," + BorrowStatus.STATUS_REPAYMENT_DONE);
        sql.append("))");*/
		sql.append("  (p1.status =" + BorrowTenderStatus.SUCCESS + " or p1.status =" + BorrowTenderStatus.PROCESSED + ")");
		sql.append("  ORDER BY p1.add_time DESC");
		sql.append(" limit 0,10"); // 10件数
		Query query = em.createNativeQuery(sql.toString());

		List<Object[]> ls = query.getResultList();
		List<TenderDynamicModel> list = Lists.newArrayList();
		for (Object[] obj : ls) {
			TenderDynamicModel model = new TenderDynamicModel();
			// 用户名
			String userName = obj[0] + "";
			model.setUserName(userName.charAt(0) + "******" + userName.charAt(userName.length() - 1));

			// 金额
			model.setTenderMoney(NumberUtil.getDouble2(obj[1] + ""));

			// 手机号
			String mobilePhone = obj[2] + "";
            if(mobilePhone.length() > 8) {
                model.setMobilePhone(mobilePhone.substring(0, 3) + "*****" + mobilePhone.substring(8));
            } else {
                model.setMobilePhone("*****");
            }
			Date addTime = (Date) obj[3];
			if (addTime != null) {
				model.setAddTime(DateUtil.dateStr9(addTime));
			}
			// 图片
			model.setAvatarPicPath(obj[4] + "");
			list.add(model);
		}
		return list;
	}

	public int getTenderCountByBorrowid(long id) {
		String sql = "SELECT COUNT(1) FROM " + Global.DB_PREFIX + "borrow_tender AS tender," + Global.DB_PREFIX + "user AS user WHERE user.userId=tender.userId and tender.borrow_id=?";
		Query query = em.createNativeQuery(sql);
		query.setParameter("id", id);
		return (Integer) query.getSingleResult();
	}

	@Override
	public long getTenderUserCountByBorrowid(long id) {
		String sql = "select count(DISTINCT user_id) from " + Global.DB_PREFIX + "borrow_tender where borrow_id=:id";
		Query query = em.createNativeQuery(sql);
		query.setParameter("id", id);
		return ((BigInteger) query.getSingleResult()).longValue();
	}

	@Override
	public PageDataList<BorrowTenderModel> getTenderListByBorrowid(long id, int page, int pernum) {
		QueryParam param = QueryParam.getInstance();
		String sql = "select t.*,collection.repay_time as repay_time,collection.repay_yes_time as repay_yes_time,collection.repay_account as repay_account,u.userName from " + Global.DB_PREFIX + "borrow_tender t "
				+ "left join " + Global.DB_PREFIX + "borrow_collection collection on t.id=collection.tender_id "
				+ "left join " + Global.DB_PREFIX + "user u on u.userId=t.userId "
				+ "left join " + Global.DB_PREFIX + "borrow borrow on t.borrow_id = borrow.id where t.borrow_id=:borrowId group by t.id";
		param.addParam("borrowId", id);
		param.addPage(page, pernum);
		return this.findPageListBySql(sql, param, BorrowTenderModel.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<BorrowTenderModel> getNewTenderList() {
		String sql = "SELECT t.borrow_id,t.account,t.add_time,b.name,c.user_name FROM " + Global.DB_PREFIX + "borrow_tender t," + Global.DB_PREFIX + "user c,"
				+ "" + Global.DB_PREFIX + "borrow b WHERE t.user_id = c.user_id AND b.id = t.borrow_id AND t.borrow_id = b.id AND "
				+ "b. STATUS IN(1, 3, 6, 7, 8) ORDER BY t.add_time DESC LIMIT 0,10";

		Query query = em.createNativeQuery(sql);
		List<Object[]> list = query.getResultList();
		List<BorrowTenderModel> list_ = new ArrayList<BorrowTenderModel>();
		for (Object[] obj : list) {
			BorrowTenderModel tenderModel = new BorrowTenderModel();
			tenderModel.setBorrowId(NumberUtil.getLong(obj[0] + ""));
			tenderModel.setAccount(NumberUtil.getDouble(obj[1] + ""));
			tenderModel.setAddTime((Date) obj[2]);
			tenderModel.setBorrowName(obj[3] + "");
			String userName = obj[4] + "";
			tenderModel.setUserName(
					userName.substring(0, 1) + "****" + userName.substring(userName.length() - 1, userName.length()));
			list_.add(tenderModel);
		}
		return list_;
	}

	@Override
	public BorrowTender addBorrowTender(BorrowTender tender) {
		return save(tender);
	}

	@Override
	public int getUserSecondTenderNum(long userId, Date beginDate, Date endDate) {
		String sql = "select count(*) from " + Global.DB_PREFIX + "borrow_tender tender, " + Global.DB_PREFIX + "borrow borrow "
				+ "where tender.user_id = :userId and tender.borrow_id = borrow.id and borrow.type="
				+ BorrowType.TYPE_SECOND
				+ " and(borrow.status in (1, 3, 6, 8)) and tender.add_time between :startTime and :endTime ";
		long startTime = DateUtil.getTime(DateUtil.getFirstSecIntegralTime(beginDate));
		long endTime = DateUtil.getTime(DateUtil.getLastSecIntegralTime(endDate));
		Query query = em.createNativeQuery(sql);
		query.setParameter("userId", userId);
		query.setParameter("startTime", startTime);
		query.setParameter("endTime", endTime);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return (Integer) obj;
		}
		return 0;
	}

	@Override
	public double getUserSecondTenderSum(long userId, Date beginDate, Date endDate) {
		String sql = "select sum(tender.account) from " + Global.DB_PREFIX + "borrow_tender tender, " + Global.DB_PREFIX + "borrow borrow "
				+ "where tender.user_id = :userId and tender.borrow_id = borrow.id and borrow.type="
				+ BorrowType.TYPE_SECOND
				+ " and(borrow.status in (1, 3, 6, 8)) and tender.add_time between :startTime and :endTime ";
		long startTime = DateUtil.getTime(DateUtil.getFirstSecIntegralTime(beginDate));
		long endTime = DateUtil.getTime(DateUtil.getLastSecIntegralTime(endDate));
		Query query = em.createNativeQuery(sql);
		query.setParameter("userId", userId);
		query.setParameter("startTime", startTime);
		query.setParameter("endTime", endTime);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return (Double) obj;
		}
		return 0;
	}

	@Override
	public double getUserTenderSum(long userId, long borrowId) {
		String jql = "select sum(account) from BorrowTender where borrow.id = :borrowId and user.userId = :userId";
		Query query = em.createQuery(jql);
		query.setParameter("borrowId", borrowId);
		query.setParameter("userId", userId);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return (Double) obj;
		}
		return 0;
	}

	@Override
	public double getBorrowTenderSum(long borrowId, int status) {
		String jql = "select sum(account) from BorrowTender where borrow.id = :borrowId and status = :status";
		Query query = em.createQuery(jql);
		query.setParameter("borrowId", borrowId);
		query.setParameter("status", status);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return (Double) obj;
		}
		return 0;
	}

	@Override
	public int getBorrowTenderTimes(long borrowId, long userId) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("borrow.borrowId", borrowId);
		param.addParam("user.userId", userId);
		return this.countByCriteria(param);
	}

	@Override
	public BorrowTender modifyBorrowTender(BorrowTender tender) {
		String sql = "update " + Global.DB_PREFIX + "borrow_tender set repayment_account = :repayment_account,wait_account = :wait_account,interest = :interest,"
				+ "wait_interest = :wait_interest, status= :status,repayment_yes_interest = :repayment_yes_interest where id = :id";
		String[] names = new String[] { "repayment_account", "wait_account", "interest", "wait_interest", "status",
				"repayment_yes_interest", "id" };
		Object[] values = new Object[] { tender.getRepaymentAccount(), tender.getWaitAccount(), tender.getInterest(),
				tender.getWaitInterest(), tender.getStatus(), tender.getRepaymentYesAccount(), tender.getId() };
		int result = this.updateBySql(sql, names, values);
		if (result < 1) {
			return null;
		}
		super.refresh(find(tender.getId()));
		return tender;
	}

	@Override
	public double sumTenderAccount(long userId) {
		String selSql = "select sum(p1.account) as num from " + Global.DB_PREFIX + "borrow_tender p1 left join " + Global.DB_PREFIX + "borrow b on b.id=p1.borrow_id"
				+ "  where b.status in(3,6,7,8) and p1.userId=:userId";
		Query query = em.createNativeQuery(selSql);
		query.setParameter("userId", userId);
		return (Double) query.getSingleResult();
	}

	@Override
	public void updateStatus(long borrowId, int status, int preStatus) {
		String sql = "UPDATE BorrowTender SET status = :status WHERE borrow.id = :borrowId AND status = :preStatus ";
		Query query = em.createQuery(sql);
		query.setParameter("borrowId", borrowId);
		query.setParameter("status", status);
		query.setParameter("preStatus", preStatus);
		int result = query.executeUpdate();
		if (result <= 0) {
			throw new UserException("更新tender记录状态失败", 1);
		}
	}

	@Override
	public void updateStatus(long id, int status) {
		String sql = "UPDATE BorrowTender SET status = :status WHERE id = :id  ";
		Query query = em.createQuery(sql);
		query.setParameter("id", id);
		query.setParameter("status", status);
		int result = query.executeUpdate();
		if (result <= 0) {
			throw new UserException("更新tender记录状态失败", 1);
		}
	}

	@Override
	public int getAutoTenderByUserId(long userId, byte tenderType, long borrowId, int status) {
		String sql = "select count(*) from " + Global.DB_PREFIX + "borrow_tender tender, " + Global.DB_PREFIX + "borrow borrow where borrow.id = tender.borrow_id "
				+ "and borrow.status = :status and tender.user_id = :user_id and tender.borrow_id = :borrow_id and tender.tender_type = :tenderType";
		Query query = em.createNativeQuery(sql);
		query.setParameter("status", status);
		query.setParameter("user_id", userId);
		query.setParameter("borrow_id", borrowId);
		query.setParameter("tenderType", tenderType);
		int times = Integer.parseInt(query.getSingleResult().toString());
		return times;
	}

	@Override
	public void modifyTenderBilNo(long id, String tenderBilNo) {
		String sql = "UPDATE BorrowTender SET tender_bil_no = :tenderBilNo WHERE id = :id ";
		Query query = em.createQuery(sql);
		query.setParameter("id", id);
		query.setParameter("tenderBilNo", tenderBilNo);
		int result = query.executeUpdate();
		if (result <= 0) {
			throw new UserException("更新tender失败", 1);
		}

	}

	@Override
	public BorrowTender getTenderByBillNo(String tenderBilNo) {
        return findObjByProperty("tenderBilNo", tenderBilNo);
	}

	@Override
	public List<BorrowTender> getTenderByBorrowId(long id) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("borrow.id", id);
		return findByCriteria(param);
	}

	@Override
	public List<BorrowTender> getTenderByUserId(long id) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("user.userId", id);
		return findByCriteria(param);
	}

	@Override
	public List<BorrowTender> find(long borrowId, int status) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("borrow.id", borrowId);
		param.addParam("status", status);
		return findByCriteria(param);
	}

	@SuppressWarnings("unchecked")
	@Override
	public PageDataList<Borrow> getBorrowlist(BorrowTenderModel model) {
		StringBuffer jpql = new StringBuffer(
				"SELECT p1.* FROM " + Global.DB_PREFIX + "borrow p1 , " + Global.DB_PREFIX + "borrow_tender p2 WHERE p1.id = p2.borrow_id");
		long userId = 0;
		Date startTime = null;
		Date endTime = null;
		String borrowName = "";
		if (model != null) {
			if ((model.getUser() != null) && (model.getUser().getUserId() > 0)) {
				jpql.append(" AND p2.user_id = :userId");
				userId = model.getUser().getUserId();
			}
			if (StringUtil.isNotBlank(model.getBorrowName()) && (model.getBorrowName().length() > 0)) {
				jpql.append(" AND p1.name like :borrowName");
				borrowName = model.getBorrowName();
			}
			if (StringUtil.isNotBlank(model.getStartTime())) {
				startTime = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
				jpql.append(" AND p1.add_time >= :startTime");
			}
			if (StringUtil.isNotBlank(model.getEndTime())) {
				endTime = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
				jpql.append(" AND p1.add_time <= :endTime");
			}
			if (model.getTime() > 0) {
				endTime = DateUtil.getDayStartTime(System.currentTimeMillis() / 1000);
				jpql.append(" AND p1.add_time >= :endTime");
				if (model.getTime() == 7) {
					endTime = DateUtil.rollDay(endTime, -7);
					endTime = DateUtil.getDayEndTime(endTime.getTime() / 1000);
				} else if ((model.getTime() > 0) && (model.getTime() < 4)) {
					endTime = DateUtil.rollMon(endTime, -model.getTime());
					endTime = DateUtil.getDayEndTime(endTime.getTime() / 1000);
				}
			}
		}
		jpql.append(" GROUP BY p1.id ORDER BY p2.add_time DESC");
		PageDataList<Borrow> pageDataList = new PageDataList<Borrow>();
		Query query = em.createNativeQuery(jpql.toString(), Borrow.class);
		if (userId > 0) {
			query.setParameter("userId", userId);
		}
		if (borrowName.length() > 0) {
			query.setParameter("borrowName", "%" + borrowName.trim() + "%");
		}
		if (startTime != null) {
			query.setParameter("startTime", startTime);
		}
		if (endTime != null) {
			query.setParameter("endTime", endTime);
		}
		Page page = new Page(query.getResultList().size(), model.getPage(), model.getSize());
		query.setFirstResult((model.getPage() - 1) * model.getSize());
		query.setMaxResults(model.getSize());
		List<Borrow> list = query.getResultList();
		pageDataList.setList(list);
		pageDataList.setPage(page);
		return pageDataList;
	}

	@Override
	public int getTenderCountByDate(String date) {
		String dateFormat = "%Y-%m-%d";
		if (date.length() == 7) {
			dateFormat = "%Y-%m";
		}
		String sql = "select count(distinct user_id) from " + Global.DB_PREFIX + "borrow_tender where date_format(add_time,'" + dateFormat
				+ "') = ?";
		Query query = em.createNativeQuery(sql).setParameter(1, date);
		Object obj = query.getSingleResult();
		int count = 0;
		if (obj != null) {
			count = Integer.parseInt((obj.toString()));
		}
		return count;
	}

	@Override
	public double tenderAccount() {
		String selSql = "SELECT SUM(t.real_amount) FROM " + Global.DB_PREFIX + "borrow b ," + Global.DB_PREFIX + "borrow_tender t WHERE t.borrow_id = b.id AND b.`status` in (1,3,6,7,8) AND t.`status` in (1,3) and b.category != 2;";
		Query query = em.createNativeQuery(selSql);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return Double.parseDouble(obj.toString());
		}
		return 0;
	}

	@Override
	public int getInvestUserCount() {
		String sql = "SELECT COUNT(DISTINCT t.user_id) FROM " + Global.DB_PREFIX + "borrow_tender t";
		Query query = em.createNativeQuery(sql);
		Object count = query.getSingleResult();
		if (count != null) {
			return Integer.parseInt(count.toString());
		}
		return 0;
	}

	@Override
	public double tenderAllInterest() {
		String sql = "SELECT SUM(t1.repayment_yes_interest) FROM " + Global.DB_PREFIX + "borrow_tender t1 WHERE t1.status = 1";
		Query query = em.createNativeQuery(sql);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return Double.parseDouble(obj.toString());
		}
		return 0;
	}

	@Override
	public double getInterestByBorrowId(long id) {
		String sql = "SELECT SUM(interest) FROM " + Global.DB_PREFIX + "borrow_tender WHERE borrow_id = ?";
		Query query = em.createNativeQuery(sql).setParameter(1, id);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return Double.parseDouble(obj.toString());
		}
		return 0;
	}

	@SuppressWarnings("unchecked")
	@Override
	public PageDataList<BorrowTenderModel> getBorrowTenderlist(BorrowTenderModel model) {
		/*
		 * StringBuffer sb = new StringBuffer("");
		 * sb.append(
		 * " SELECT * FROM (SELECT bt.borrow_id,rb.name,rb.scales,SUM((IFNULL(bc1.capital1,0)+IFNULL(bc2.capital2,0))) AS capital,IFNULL(SUM(bc1.repayment_yes_account),0) AS yesRepay, IFNULL(SUM(bc2.waitaccount),0) AS waitCollion ,rp.path from " + Global.DB_PREFIX + "borrow_tender bt LEFT JOIN "
		 * );
		 * sb.append(
		 * " (SELECT bc.borrow_id,bc.tender_id,SUM(bc.capital - bc.bond_capital) AS capital1, SUM(bc.repayment_yes_account) AS repayment_yes_account FROM " + Global.DB_PREFIX + "borrow_collection bc where bc.status = 1 AND bc.user_id = :userId GROUP BY bc.borrow_id,bc.tender_id ) bc1 "
		 * );
		 * sb.append(" ON bt.borrow_id = bc1.borrow_id AND bt.id= bc1.tender_id LEFT JOIN ");
		 * sb.append(
		 * " (SELECT bc.borrow_id,bc.tender_id,SUM(bc.capital - bc.bond_capital) AS capital2,SUM(bc.repayment_account - bc.bond_capital - bc.bond_interest - bc.bond_award) AS waitaccount FROM " + Global.DB_PREFIX + "borrow_collection bc where bc.status = 0 AND bc.user_id = :userId GROUP BY bc.borrow_id,bc.tender_id) bc2 "
		 * );
		 * sb.append(" ON bt.borrow_id = bc2.borrow_id AND bt.id= bc2.tender_id, " + Global.DB_PREFIX + "borrow rb");
		 * sb.append(
		 * "left join (select rdup.pic_path as path ,rdup.borrow_id as bid from " + Global.DB_PREFIX + "borrow_upload rdup  where rdup.type = 5 ) as rp on rp.bid=rb.id  "
		 * );
		 * sb.append(
		 * " WHERE rb.id = bt.borrow_id AND bt.user_id=:userId AND (rb.status IN(3, 6, 7 ,69) OR (rb.status=1 AND rb.expiration_time > now())) "
		 * );
		 * sb.append(" GROUP BY bt.borrow_id ORDER BY bt.add_time DESC  ) tb WHERE capital > 0");
		 */
		StringBuffer sb = new StringBuffer("");
		sb.append(
				"SELECT * FROM (SELECT bt.borrow_id,rb.name,rb.scales,SUM((IFNULL(bc1.capital1,0)+IFNULL(bc2.capital2,0))) AS capital,IFNULL(SUM(bc1.repayment_yes_account),0) AS yesRepay, IFNULL(SUM(bc2.waitaccount),0) AS waitCollion ,rp.path ,bt.id,ifnull(bt.status,0),rb.uuid,rb.type  from " + Global.DB_PREFIX + "borrow_tender bt ");
		sb.append(
				"LEFT JOIN  (SELECT bc.borrow_id,bc.tender_id,SUM(bc.capital - bc.bond_capital) AS capital1, SUM(bc.repayment_yes_account) AS repayment_yes_account FROM " + Global.DB_PREFIX + "borrow_collection bc where bc.status = 1 AND bc.user_id = :userId GROUP BY bc.borrow_id,bc.tender_id ) bc1  ON bt.borrow_id = bc1.borrow_id AND bt.id= bc1.tender_id ");
		sb.append(
				"LEFT JOIN  (SELECT bc.borrow_id,bc.tender_id,SUM(bc.capital - bc.bond_capital) AS capital2,SUM(bc.repayment_account - bc.bond_capital - bc.bond_interest - bc.bond_award) AS waitaccount FROM " + Global.DB_PREFIX + "borrow_collection bc where bc.status = 0 AND bc.user_id = :userId GROUP BY bc.borrow_id,bc.tender_id) bc2  ON bt.borrow_id = bc2.borrow_id AND bt.id= bc2.tender_id, " + Global.DB_PREFIX + "borrow rb ");
		sb.append(
				"left join (select rdup.pic_path as path ,rdup.borrow_id as bid from " + Global.DB_PREFIX + "borrow_upload rdup  where rdup.type = 5 ) as rp on rp.bid=rb.id  ");
		sb.append(
				"WHERE  rb.id = bt.borrow_id AND bt.user_id=:userId AND (rb.status IN(3, 6, 7 ,8) AND (bt.status!=9)) GROUP BY bt.borrow_id ORDER BY bt.add_time DESC  ) tb WHERE capital > 0 ");

		Query query = em.createNativeQuery(sb.toString());
		query.setParameter("userId", model.getUserId());

		Page page = new Page(query.getResultList().size(), model.getPage(), model.getSize());
		query.setFirstResult((model.getPage() - 1) * model.getSize());
		query.setMaxResults(model.getSize());
		List<Object[]> list = query.getResultList();
		List<BorrowTenderModel> modelList = new ArrayList<BorrowTenderModel>();
		PageDataList<BorrowTenderModel> pageDataList = new PageDataList<BorrowTenderModel>();
		for (Object[] o : list) {
			BorrowTenderModel bt = new BorrowTenderModel();
			bt.setBorrowId((Integer) o[0]);
			bt.setBorrowName(o[1] + "");
			bt.setScales(Double.parseDouble((o[2] + "")));
			bt.setTotalTender(Double.parseDouble((o[3] + "")));
			bt.setYesRepay(Double.parseDouble((o[4] + "")));
			bt.setWaitCollection(Double.parseDouble((o[5] + "")));
			bt.setContractPath(o[6] + "");
			bt.setId((Integer) o[7]);
			bt.setStatus((Integer) o[8]);
			bt.setUuid(o[9] + "");
			bt.setBorrowType((Integer) o[10]);
			modelList.add(bt);
		}
		pageDataList.setList(modelList);
		pageDataList.setPage(page);
		return pageDataList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public PageDataList<BorrowTenderModel> getTenderRecordlist(BorrowTenderModel model) {
		StringBuffer sb = new StringBuffer("");
		sb.append(
				" SELECT * FROM (SELECT bt.borrow_id,rb.name,rb.apr,if(SUM((IFNULL(bc1.capital1,0)+IFNULL(bc2.capital2,0)))=0,bt.money,SUM((IFNULL(bc1.capital1,0)+IFNULL(bc2.capital2,0)))) AS capital,rb.review_time,rb.borrow_time_type,rb.time_limit,IFNULL(SUM(bc1.yesInterest),0) AS yesInterest, IFNULL(SUM(bc2.waitInterest),0) AS waitInterest ,IFNULL(ratein.upi,0),rb.uuid,IFNULL(bt.interest,0),IFNULL(ratein.upapr,0),rb.type,rb.category,bt.add_time AS borrow_tender_time,bt.repayment_time AS ex_repayment_time,IFNULL(bt.`status`, 0) from " + Global.DB_PREFIX + "borrow_tender bt LEFT JOIN ");

		sb.append(
				" (select rate.up_interest as upi,rate.tender_id as ratetid ,rate.up_apr as upapr from  " + Global.DB_PREFIX + "additional_use_rate_log as rate ) as ratein on bt.id =ratein.ratetid  left join ");

		sb.append(
				" (SELECT bc.borrow_id,bc.tender_id,SUM(bc.capital - bc.bond_capital) AS capital1, SUM(bc.interest + bc.repay_award - bc.bond_interest - bc.bond_award) AS yesInterest FROM " + Global.DB_PREFIX + "borrow_collection bc where bc.status = 1 AND bc.user_id = :userId GROUP BY bc.borrow_id,bc.tender_id ) bc1 ");
		sb.append(" ON bt.borrow_id = bc1.borrow_id AND bt.id= bc1.tender_id LEFT JOIN ");
		sb.append(
				" (SELECT bc.borrow_id,bc.tender_id,SUM(bc.capital - bc.bond_capital) AS capital2,SUM(bc.interest + bc.repay_award - bc.bond_interest - bc.bond_award) AS waitInterest FROM " + Global.DB_PREFIX + "borrow_collection bc where bc.status = 0 AND bc.user_id = :userId GROUP BY bc.borrow_id,bc.tender_id) bc2 ");
		sb.append(" ON bt.borrow_id = bc2.borrow_id AND bt.id= bc2.tender_id, " + Global.DB_PREFIX + "borrow rb ");
		sb.append(" WHERE rb.id = bt.borrow_id AND bt.user_id=:userId ");

		if (model.getStatus() == BorrowStatus.STATUS_REPAYMENT_DONE) {
			sb.append(" AND rb.status = 8 ");
		} else if (model.getStatus() == 0) {
			// 全部的场合，不加Status条件
		} else if (model.getStatus() == BorrowStatus.STATUS_TRIAL_PASSED) {
			sb.append("AND rb.status = 1 ");
		} else {
			sb.append(" AND (rb.status IN(3, 6, 7) ) ");
		}

		Date d = DateUtil.getDate((System.currentTimeMillis() / 1000) + "");
		Date startTime = null;
		Date endTime = null;
		if (model.getTime() == 7) {
			sb.append(" AND bt.add_time >= :startTime");
			sb.append(" AND bt.add_time <= :endTime");
			startTime = DateUtil.rollDay(d, -7);
			endTime = d;
		} else if ((model.getTime() > 0) && (model.getTime() < 4)) {
			sb.append(" AND bt.add_time >= :startTime");
			sb.append(" AND bt.add_time <= :endTime");
			startTime = DateUtil.rollMon(d, -model.getTime());
			endTime = d;
		}
		if (StringUtil.isNotBlank(model.getStartTime())) {
			startTime = DateUtil.valueOf(model.getStartTime() + " 00:00:00");
			sb.append(" AND bt.add_time > :startTime");
		}
		if (StringUtil.isNotBlank(model.getEndTime())) {
			endTime = DateUtil.valueOf(model.getEndTime() + " 23:59:59");
			sb.append(" AND bt.add_time < :endTime");
		}
		sb.append(" GROUP BY bt.borrow_id,bt.id ORDER BY bt.add_time DESC ) tb WHERE capital > 0");

		Query query = em.createNativeQuery(sb.toString());
		query.setParameter("userId", model.getUser().getUserId());
		if (startTime != null) {
			query.setParameter("startTime", startTime);
		}
		if (endTime != null) {
			query.setParameter("endTime", endTime);
		}
		Page page = new Page(query.getResultList().size(), model.getPage(), model.getSize());
		query.setFirstResult((model.getPage() - 1) * model.getSize());
		query.setMaxResults(model.getSize());
		List<Object[]> list = query.getResultList();
		List<BorrowTenderModel> modelList = new ArrayList<BorrowTenderModel>();
		PageDataList<BorrowTenderModel> pageDataList = new PageDataList<BorrowTenderModel>();
		for (Object[] o : list) {
			BorrowTenderModel bt = new BorrowTenderModel();
			bt.setBorrowId((Integer) o[0]);
			bt.setBorrowName(o[1] + "");
			bt.setApr(Double.parseDouble((o[2] + "")));
			bt.setMoney(Double.parseDouble((o[3] + "")));
			if ((Byte) o[14] == 2) {// 体验标
				bt.setStartDate((Date) o[15]);
			} else {
				bt.setStartDate((Date) o[4]);
			}
			if ((bt.getStartDate() != null) && ((Byte) o[14] != 2)) {// 排除体验标
				if ((Boolean) o[5] == false) {
					bt.setEndDate(DateUtil.rollMon(bt.getStartDate(), (Integer) o[6]));
				} else {
					bt.setEndDate(DateUtil.rollDay(bt.getStartDate(), (Integer) o[6]));
				}
			} else {
				bt.setEndDate((Date) o[16]);
			}
			bt.setRepaymentYesInterest(Double.parseDouble((o[7] + "")));
			bt.setWaitInterest(Double.parseDouble((o[8] + "")));
			bt.setUpInterest(Double.parseDouble((o[9] + "")));
			bt.setUuid(o[10] + "");
			bt.setInterest(Double.parseDouble((o[11] + "")));
			bt.setUpApr(Double.parseDouble((o[12] + "")));
			bt.setBorrowType((Integer) o[13]);
			bt.setStatus((Integer) o[17]);
			modelList.add(bt);
		}

		pageDataList.setList(modelList);
		pageDataList.setPage(page);
		return pageDataList;
	}

	@Override
	public double getCountByTypeAndDate(int type, int i, String date) {
		StringBuffer sb = new StringBuffer("select ");
		if (type == 1) {
			sb.append("sum(money)");
		} else {
			sb.append("count(distinct user_id)");
		}
		sb.append("from " + Global.DB_PREFIX + "borrow_tender where date_format(add_time,'%Y-%m') = ? and ");
		if (i == 0) {
			sb.append("money between 0 and 9999");
		} else if (i == 1) {
			sb.append("money between 10000 and 99999");
		} else if (i == 2) {
			sb.append("money between 100000 and 499999");
		} else if (i == 3) {
			sb.append("money between 500000 and 999999");
		} else {
			sb.append("money > 1000000");
		}
		Query query = em.createNativeQuery(sb.toString()).setParameter(1, date);
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj + "");
		}
		return result;
	}

	@Override
	public double getCumulativeTenderByDate(String date) {
		StringBuffer sb = new StringBuffer(
				"select count(distinct user_id) from " + Global.DB_PREFIX + "borrow_tender where date_format(add_time,'");
		if (date.length() == 4) {
			sb.append("%Y");
		} else {
			sb.append("%Y-%m");
		}
		sb.append("') = ?");
		Query query = em.createNativeQuery(sb.toString()).setParameter(1, date);
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj + "");
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getTenderCardIdsByDate(String date) {
		String sql = "select distinct(card_id) from " + Global.DB_PREFIX + "user u, " + Global.DB_PREFIX + "borrow_tender t where u.user_id=t.user_id and date_format(t.add_time,'%Y-%m')=?";
		Query query = em.createNativeQuery(sql).setParameter(1, date);
		return query.getResultList();
	}

	@Override
	public double getTotalMoneyByTender(BorrowTender tender) {
		String sql = "select sum(money) from " + Global.DB_PREFIX + "borrow_tender where status = 1 and date_format(add_time,'%Y-%m-%d')=? and user_id=?";
		Query query = em.createNativeQuery(sql).setParameter(1, DateUtil.dateStr2(tender.getAddTime())).setParameter(2,
				tender.getUser().getUserId());
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj + "");
		}
		return result;
	}

	@Override
	public double getTotalMoneyByUserVip(UserVip vip) {
		String sql = "select sum(money) from " + Global.DB_PREFIX + "borrow_tender where add_time between ? and ? and user_id=?";
		Query query = em.createNativeQuery(sql).setParameter(1, DateUtil.rollYear(vip.getVipTime(), -1))
				.setParameter(2, vip.getVipTime()).setParameter(3, vip.getUserId());
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj + "");
		}
		return result;
	}

	@Override
	public double getTenderUseRate(long tenderId) {
		String sql = "select sum(IFNULL(up_apr,0)) from " + Global.DB_PREFIX + "additional_use_rate_log where tender_id = ?1";
		Query query = em.createNativeQuery(sql);
		query.setParameter(1, tenderId);
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj + "");
		}
		return result;
	}

	@Override
	public double getTenderUseRed(long tenderId, String typeId) {
		String sql = "select sum(IFNULL(amount,0)) from " + Global.DB_PREFIX + "additional_user_red_envelope where key_id =?1 and use_type =?2";
		Query query = em.createNativeQuery(sql);
		query.setParameter(1, tenderId);
		query.setParameter(2, typeId);
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj + "");
		}
		return result;
	}

	@Override
	public double getTenderUseExper(long tenderId) {
		String sql = "select sum(IFNULL(capital,0)) from " + Global.DB_PREFIX + "additional_experience_tender where tender_id = ?1";
		Query query = em.createNativeQuery(sql);
		query.setParameter(1, tenderId);
		Object obj = query.getSingleResult();
		double result = 0;
		if (obj != null) {
			result = Double.parseDouble(obj + "");
		}
		return result;
	}

	@Override
	public int countTenderByUser(User user, int category) {
		QueryParam param = QueryParam.getInstance().addParam("user", user);
		QueryParam newCheck = QueryParam.getInstance().addParam("user", user).addParam("borrow.category", 0);
		QueryParam experienceCheck = QueryParam.getInstance().addParam("user", user).addParam("borrow.category",2);
		SearchFilter orFilter1 = new SearchFilter("borrow.status", Operators.EQ, BorrowStatus.STATUS_TRIAL_PASSED);
		SearchFilter orFilter2 = new SearchFilter("borrow.status", Operators.EQ, BorrowStatus.STATUS_RECHECK_PASS);
		SearchFilter orFilter3 = new SearchFilter("borrow.status", Operators.EQ, BorrowStatus.STATUS_REPAYMENT_START);
		SearchFilter orFilter4 = new SearchFilter("borrow.status", Operators.EQ, BorrowStatus.STATUS_REPAYMENT_DOING);
		SearchFilter orFilter5 = new SearchFilter("borrow.status", Operators.EQ, BorrowStatus.STATUS_REPAYMENT_DONE);
		newCheck.addOrAndFilter(orFilter1, orFilter2, orFilter3, orFilter4, orFilter5);
		experienceCheck.addOrAndFilter(orFilter1, orFilter2, orFilter3, orFilter4, orFilter5);
		param.addOrAndFilter(orFilter1, orFilter2, orFilter3, orFilter4, orFilter5);
		if (category == 0) {
			if (this.countByCriteria(experienceCheck) == this.countByCriteria(param)) {
				return 0;
			} else {
				return 1;
			}
		} else {
			if (this.countByCriteria(newCheck) == this.countByCriteria(param)) {
				return 0;
			} else {
				return 1;
			}
		}
	}

	@Override
	public List<BorrowTenderModel> repaymentExperience() {
		String sql = "SELECT t.borrow_id,t.account,t.add_time,b.name,c.user_name,t.repayment_time,t.user_id,t.interest,t.id "
				+ "FROM " + Global.DB_PREFIX + "borrow_tender t, " + Global.DB_PREFIX + "user c," + Global.DB_PREFIX
				+ "borrow b WHERE t.user_id = c.user_id AND t.borrow_id = b.id AND "
				+ " type=112 and category=2 and repayment_time <= NOW() and t.status=1 and c.tpp_user_cust_id is not null ";

		Query query = em.createNativeQuery(sql);
		List<Object[]> list = query.getResultList();
		List<BorrowTenderModel> list_ = new ArrayList<BorrowTenderModel>();
		for (Object[] obj : list) {
			BorrowTenderModel tenderModel = new BorrowTenderModel();
			tenderModel.setBorrowId(NumberUtil.getLong(obj[0] + ""));
			tenderModel.setAccount(NumberUtil.getDouble(obj[1] + ""));
			tenderModel.setAddTime((Date) obj[2]);
			tenderModel.setBorrowName(obj[3] + "");
			String userName = obj[4] + "";
			tenderModel.setUserName(
					userName.substring(0, 1) + "****" + userName.substring(userName.length() - 1, userName.length()));
			tenderModel.setRepaymentTime((Date) obj[5]);
			tenderModel.setUserId(NumberUtil.getLong(obj[6] + ""));
			tenderModel.setInterest(NumberUtil.getDouble(obj[7] + ""));
			tenderModel.setId(NumberUtil.getLong(obj[8] + ""));
			list_.add(tenderModel);
		}
		return list_;
	}

	@Override
	public void modifyTenderStatus(long id, int status, double interest) {
		String sql = "UPDATE BorrowTender SET status = :status,repaymentYesAccount=:repaymentYesAccount,repaymentYesInterest=:repaymentYesInterest WHERE id = :id ";
		Query query = em.createQuery(sql);
		query.setParameter("id", id);
		query.setParameter("status", status);
		query.setParameter("repaymentYesAccount", interest);
		query.setParameter("repaymentYesInterest", interest);
		int result = query.executeUpdate();
		if (result <= 0) {
			throw new UserException("更新tender失败", 1);
		}

	}

	@Override
	public Boolean checkTenderByUserId(long id) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("user.userId", id);
		SearchFilter filter=new SearchFilter("borrow.category",Operators.NOTEQ,2);
		param.addOrAndFilter(filter);
		if(this.countByCriteria(param)>0)
			return false;
		else
			return true;
	}

	@Override
	public double todayTenderAccount() {
		String selSql = "SELECT SUM(t.real_amount) FROM " + Global.DB_PREFIX + "borrow b ," + Global.DB_PREFIX + "borrow_tender t WHERE t.borrow_id = b.id AND b.`status` in (1,3,6,7,8) AND t.`status` in (1,3) and b.category != 2 and t.add_time>=?;";
		Query query = em.createNativeQuery(selSql).setParameter(1, DateUtil.dateStr2(DateUtil.getNow()));
		Object obj = query.getSingleResult();
		if (obj != null) {
			return Double.parseDouble(obj.toString());
		}
		return 0;
	}

	@Override
	public PageDataList<BorrowTenderModel> listInvestor(long borrowId, int page, int size) {
		QueryParam param = QueryParam.getInstance();
		String selectSql = "select * ";
		String fromSql = " FROM " + Global.DB_PREFIX + "borrow_tender";
		param.addParam("borrow_id", borrowId);
		param.addPage(page, size);
		param.addOrder(OrderType.DESC,"add_time");
		PageDataList<BorrowTender> borrowTenderPageData= findPageListBySql(selectSql,fromSql,param, BorrowTender.class);
		List<BorrowTenderModel> borrowTenderModelList = new ArrayList<BorrowTenderModel>();
		List<BorrowTender> borrowTenderList =borrowTenderPageData.getList();
		for (BorrowTender borrowTender : borrowTenderList) {
			BorrowTenderModel borrowTenderModel = new BorrowTenderModel();
			String username = borrowTender.getUser().getUserName();
			borrowTenderModel.setUserName(username.charAt(0) + "******" + username.charAt(username.length() - 1));
			borrowTenderModel.setAddTime(borrowTender.getAddTime());
			borrowTenderModel.setMoney(borrowTender.getMoney());
			borrowTenderModelList.add(borrowTenderModel);
		}
		PageDataList<BorrowTenderModel> BorrowTenderModelPage = new PageDataList<BorrowTenderModel>();
		BorrowTenderModelPage.setList(borrowTenderModelList);
		BorrowTenderModelPage.setPage(borrowTenderPageData.getPage());
		BorrowTenderModelPage.setType(borrowTenderPageData.getType());
		return BorrowTenderModelPage;
	}

	@Override
	public Double getTenderAmountByDate(String date) {
		String dateFormat = "%Y-%m-%d";
		if (date.length() == 7) {
			dateFormat = "%Y-%m";
		}
		String sql = "select SUM(money) from "+ Global.DB_PREFIX +"borrow_tender where date_format(add_time,'" + dateFormat
				+ "') = ? AND status NOT IN (2,9)";
		Query query = em.createNativeQuery(sql).setParameter(1, date);
		Object obj = query.getSingleResult();
		double amount = 0;
		if (obj != null) {
			amount = Double.parseDouble(((obj.toString())));
		}
		return amount;
	}
	
	@Override
	public double tenderAccountSum(long borrowId,long userId) {
		String selSql = "SELECT SUM(bt.real_amount) FROM " + Global.DB_PREFIX + "borrow_tender bt WHERE bt.borrow_id = ?1 AND bt.user_id = ?2 ";
		Query query = em.createNativeQuery(selSql);
		query.setParameter(1, borrowId).setParameter(2, userId);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return Double.parseDouble(obj.toString());
		}
		return 0;
	}

	@Override
	public BorrowTender getUserFirstTender(long userId) {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT * FROM " + Global.DB_PREFIX + "borrow_tender where  user_id = :userId and status in (0,1,3) order by add_time asc");
		Query query = em.createNativeQuery(sb.toString(), BorrowTender.class);
		query.setParameter("userId", userId);
		return (BorrowTender)query.getResultList().get(0);
	}

	@Override
	public BorrowTender getBorrowTenderAmountByBorrowId(long borrowId, long userId) {
		String selSql = "SELECT SUM(bt.account),SUM(bt.repayment_account) FROM " + Global.DB_PREFIX + "borrow_tender bt WHERE bt.borrow_id = ?1 AND bt.user_id = ?2 AND bt.status in (1,3)";
		Query query = em.createNativeQuery(selSql);
		query.setParameter(1, borrowId).setParameter(2, userId);
		Object[] object= (Object[]) query.getSingleResult();
		if(object!=null&&object.length>0){
			BorrowTender borrowTender = new BorrowTender();
			borrowTender.setAccount(Double.parseDouble(object[0]+""));
			borrowTender.setRepaymentAccount(Double.parseDouble(object[1]+""));
			return borrowTender;
		}
		else {
			return null;
		}
	}

}
